package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 * Created by Thinkpad on 2016/12/9.
 */
public class CommentDb {
    private static DatabaseBasic mSqlHelper;

    public static void insertComment(String studentId, int courseId, int rating1, int rating2,
                                     int rating3, String comment){
        String sql = "insert into courseComment(studentId,courseId,rating1,rating2,rating3,comment)" +
                "values(\'" +
                studentId + "\'," +
                courseId + "," +
                rating1 + "," +
                rating2 + "," +
                rating3 + ",\'" +
                comment + "\')";
        execSql(sql);
    }

    public static void updateComment(String studentId, int courseId, int rating1, int rating2,
                                     int rating3, String comment){
        String sql = "update courseComment set rating1=" + rating1 + ",rating2=" + rating2 + ",rating3=" +
                rating3 + ",comment=\'" + comment + "\' where studentId=\'" + studentId + "\' and " +
                "courseId=" + courseId;
        execSql(sql);
    }

    private static void execSql(String sql) {
        mSqlHelper = new DatabaseBasic();
        mSqlHelper.executeSqlUpdate(sql);
        mSqlHelper.recycle();
    }

    public static CourseComment selectComment(String studentId, int courseId){
        CourseComment comment = null;
        String sql = "select * from coursecomment where studentId = \'" + studentId +
                "\'" + " and courseId = " + courseId;
        mSqlHelper = new DatabaseBasic();
        ResultSet resultSet = mSqlHelper.executeSql(sql);
        comment = getComment(resultSet, mSqlHelper);
        return comment;
    }

    private static CourseComment getComment(ResultSet resultSet, DatabaseBasic mSqlHelper) {
        CourseComment comment = null;
        try {
            if (resultSet.next()) {
                comment = new CourseComment(resultSet.getString("studentId"),
                        resultSet.getInt("courseId"),
                        resultSet.getInt("rating1"),
                        resultSet.getInt("rating2"),
                        resultSet.getInt("rating3"),
                        resultSet.getString("comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            mSqlHelper.recycle();
        }
        return comment;
    }

    /*
    * 返回某个学生对某门课是否已评价，返回true或者false
    */
    public static boolean isCommented(String studentId, int courseId){
        String sql = "select count(*) from coursecomment where studentId = \'" + studentId + "\'"
                + " and courseId = " + courseId;
        mSqlHelper = new DatabaseBasic();
        ResultSet resultSet = mSqlHelper.executeSql(sql);
        try {
            if (resultSet.next()) {
                return resultSet.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            mSqlHelper.recycle();
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /*
    * 根据courseId把某门课的所有评价提取出来
    */
    public static List<CourseComment> selectCommentsByCourseId(int courseId, int start, int length){
        List<CourseComment> comments = new ArrayList<>();
        String sql = "select * from coursecomment where courseId = " + courseId
                + " limit " + start + "," + length;
        mSqlHelper = new DatabaseBasic();
        ResultSet resultSet = mSqlHelper.executeSql(sql);
        getComments(comments, resultSet);
        return comments;
    }

    private static void getComments(List<CourseComment> result, ResultSet resultSet) {
        try {
            while (resultSet.next()) {
                CourseComment comment = new CourseComment(resultSet.getString("studentId"),
                        resultSet.getInt("courseId"),
                        resultSet.getInt("rating1"),
                        resultSet.getInt("rating2"),
                        resultSet.getInt("rating3"),
                        resultSet.getString("comment"));
                result.add(comment);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            mSqlHelper.recycle();
        }
    }

    /*
    * 根据courseId提取某门课的平均分
    */
    public static float[] selectAvgRatingByCourseId(int courseId){
        String sql = "select avg(rating1) rating1, avg(rating2) rating2, avg(rating3) rating3" +
                " from coursecomment where courseId = " + courseId;
        mSqlHelper = new DatabaseBasic();
        ResultSet resultSet = mSqlHelper.executeSql(sql);
        float[] avgRatings = new float[3];
        try {
            if (resultSet.next()) {
                for (int i = 0; i < 3; i++) {
                    avgRatings[i] = resultSet.getFloat("rating" + (i + 1));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            mSqlHelper.recycle();
        }
        return avgRatings;
    }

    public static List<Comment> selectQuitReasonsByCourseId(int courseId, int start, int length){
        List<Comment> reasons = new ArrayList<Comment>();
        String sql = "select * from quitcourse where courseId = " + courseId
                + " limit " + start + ", " + length;
        mSqlHelper = new DatabaseBasic();
        ResultSet resultSet = mSqlHelper.executeSql(sql);
        try {
            while(resultSet.next()) {
                Comment comment = new Comment(resultSet.getString("studentId"),
                        resultSet.getString("reason"),
                        resultSet.getInt("courseId"));
                reasons.add(comment);
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return reasons;
    }

    public static void insertQuitReason(String studentId, int courseId, String reason) {
        String sql = "insert into quitcourse(studentId,courseId,reason) values(\'" + studentId +
                "\'," + courseId + ",\'" + reason + "\')";
        execSql(sql);
    }

    public static void deleteQuitReason(String studentId, int courseId) {
        String sql = "delete from quitcourse where studentId=\'" + studentId + "\' and courseId=" +
                courseId;
        execSql(sql);
    }
}
